﻿using System.Data;
using Common.DBUtility;
using Research.IDAL;
using Research.Model;

namespace Research.SQLDAL
{
    public class RprjMain : IRprjMain
    {
        private readonly DbHelper _dbHelp = DbFactory.GetDbHelper("ResearchDB");

        public DataSet ShowPageRprjMain(int pageIndex, out int totalRecord)
        {
            return
                _dbHelp.GetPagedDataSet(
                    @"select RprjID,RprjNum,RprjName,RprjLevel,SubjectName,DepartName,PrjNum,UserName,SumAmount,dbo.ufn_DelayDays(RprjID) AS DelayDays,                                           
                                            case when exists (select * from RprjData where RprjID=vRprjMainShow.RprjID and DataTypeID=1) then 1 else 0 end as HasFoundData,
                                            case when exists (select * from RprjData where RprjID=vRprjMainShow.RprjID and DataTypeID=2) then 1 else 0 end as HasProcessData,
                                            case when exists (select * from RprjData where RprjID=vRprjMainShow.RprjID and DataTypeID=3) then 1 else 0 end as HasFinishData
                                            from vRprjMainShow",
                    pageIndex, 20, "RprjID", out totalRecord);
        }

        public int InsertRprjMain(MRprjMain mRprjMain)
        {
            var cmd =
                _dbHelp.GetSqlStringCommand(
                    "INSERT INTO RprjMain VALUES(@RprjNum,@RprjName,@RprjPlatformID,@RprjRoleID)");
            _dbHelp.AddInParameter(cmd, "@RprjNum", DbType.String, mRprjMain.RprjNum);
            _dbHelp.AddInParameter(cmd, "@RprjName", DbType.String, mRprjMain.RprjName);
            _dbHelp.AddInParameter(cmd, "@RprjPlatformID", DbType.Int16, mRprjMain.RprjPlatformID);
            _dbHelp.AddInParameter(cmd, "@RprjRoleID", DbType.Int16, mRprjMain.RprjRoleID);

            return _dbHelp.ExecuteInsert(cmd);
        }

        public void InsertRprjChargePer(MRprjChargePer chargePer, int rPrjID)
        {
            var cmd = _dbHelp.GetSqlStringCommand("INSERT INTO RprjChargePer VALUES(@RprjID,@ChargePer)");
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, rPrjID);
            _dbHelp.AddInParameter(cmd, "@ChargePer", DbType.String, chargePer.ChargePerID);
            _dbHelp.ExecuteNonQuery(cmd);
        }

        public void InsertRprjDep(MRprjDep prjDep, int rPrjID)
        {
            var cmd = _dbHelp.GetSqlStringCommand("INSERT INTO RprjDep VALUES(@RprjID,@DepID)");
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, rPrjID);
            _dbHelp.AddInParameter(cmd, "@DepID", DbType.Int16, prjDep.DepID);
            _dbHelp.ExecuteNonQuery(cmd);
        }

        public void InsertRprjFund(MRprjFund prjFund, int rPrjID)
        {
            var cmd = _dbHelp.GetSqlStringCommand("INSERT INTO RprjFund VALUES(@RprjID,@FundID,@Amount,@FundDate,@Memo)");
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, rPrjID);
            _dbHelp.AddInParameter(cmd, "@FundID", DbType.Int16, prjFund.FundID);
            _dbHelp.AddInParameter(cmd, "@Amount", DbType.Double, prjFund.Amount);
            _dbHelp.AddInParameter(cmd, "@FundDate", DbType.String, prjFund.AddDate);
            _dbHelp.AddInParameter(cmd, "@Memo", DbType.String, prjFund.Memo);
            _dbHelp.ExecuteNonQuery(cmd);
        }

        public void InsertRprjPrj(MRprjPrj prj, int rPrjID)
        {
            var cmd = _dbHelp.GetSqlStringCommand("INSERT INTO RprjProject VALUES(@RprjID,@PrjID)");
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, rPrjID);
            _dbHelp.AddInParameter(cmd, "@PrjID", DbType.String, prj.PrjID);
            _dbHelp.ExecuteNonQuery(cmd);
        }

        public void InsertRprjSubject(MRprjSubject subject, int rPrjID)
        {
            var cmd = _dbHelp.GetSqlStringCommand("INSERT INTO RprjSubject VALUES(@RprjID,@RprjSubjectID)");
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, rPrjID);
            _dbHelp.AddInParameter(cmd, "@RprjSubjectID", DbType.Int16, subject.RprjSubjectID);
            _dbHelp.ExecuteNonQuery(cmd);
        }

        public void UpdateRprjMain(MRprjMain mRprjMain)
        {
            var cmd =
                _dbHelp.GetSqlStringCommand(
                    "UPDATE RprjMain SET RprjNum=@RprjNum,RprjName=@RprjName,RprjPlatformID=@RprjPlatformID,RprjRoleID=@RprjRoleID WHERE RprjID=@RprjID");
            _dbHelp.AddInParameter(cmd, "@RprjNum", DbType.String, mRprjMain.RprjNum);
            _dbHelp.AddInParameter(cmd, "@RprjName", DbType.String, mRprjMain.RprjName);
            _dbHelp.AddInParameter(cmd, "@RprjPlatformID", DbType.Int16, mRprjMain.RprjPlatformID);
            _dbHelp.AddInParameter(cmd, "@RprjRoleID", DbType.Int16, mRprjMain.RprjRoleID);
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int16, mRprjMain.RprjID);

            _dbHelp.ExecuteNonQuery(cmd);
        }

        public void DelRprjChargePer(int rPrjID)
        {
            _dbHelp.ExecuteNonQuery("DELETE FROM RprjChargePer WHERE RprjID=" + rPrjID.ToString());
        }

        public void DelRprjDep(int rPrjID)
        {
            _dbHelp.ExecuteNonQuery("DELETE FROM RprjDep WHERE RprjID=" + rPrjID.ToString());
        }

        public void DelRprjFund(int rPrjID)
        {
            _dbHelp.ExecuteNonQuery("DELETE FROM RprjFund WHERE RprjID=" + rPrjID.ToString());
        }

        public void DelRprjPrj(int rPrjID)
        {
            _dbHelp.ExecuteNonQuery("DELETE FROM RprjProject WHERE RprjID=" + rPrjID.ToString());
        }

        public void DelRprjSubject(int rPrjID)
        {
            _dbHelp.ExecuteNonQuery("DELETE FROM RprjSubject WHERE RprjID=" + rPrjID.ToString());
        }

        public int RprjAutoNumPlus(int rPrjID)
        {
            _dbHelp.ExecuteNonQuery(
                _dbHelp.ExecuteScalar<int>(string.Format("SELECT COUNT(*) FROM ScheduleAutoNum WHERE RprjID={0}",
                                                         rPrjID)) > 0
                    ? string.Format("UPDATE ScheduleAutoNum SET AutoNum=AutoNum+1 WHERE  RprjID={0}", rPrjID)
                    : string.Format("INSERT INTO ScheduleAutoNum VALUES({0},1)", rPrjID));

            return _dbHelp.ExecuteScalar<int>(string.Format("SELECT AutoNum FROM ScheduleAutoNum WHERE RprjID={0}", rPrjID));
        }

        public void InsertRprjPlanSchedule(MRprjPlanSchedule mRprjSchedule)
        {
            var cmd = _dbHelp.GetSqlStringCommand("INSERT INTO RprjSchedule VALUES(@RprjID,@AutoNum,@ScheduleName,@ScheduleSort,@PlanBegDate,@PlanEndDate,@PlanMemo)");
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, mRprjSchedule.RprjID);
            _dbHelp.AddInParameter(cmd, "@AutoNum", DbType.Int32, mRprjSchedule.AutoNum);
            _dbHelp.AddInParameter(cmd, "@ScheduleName", DbType.String, mRprjSchedule.ScheduleName);
            _dbHelp.AddInParameter(cmd, "@ScheduleSort", DbType.Int16, mRprjSchedule.ScheduleSort);
            _dbHelp.AddInParameter(cmd, "@PlanBegDate", DbType.String, mRprjSchedule.PlanBegDate);
            _dbHelp.AddInParameter(cmd, "@PlanEndDate", DbType.String, mRprjSchedule.PlanEndDate);
            _dbHelp.AddInParameter(cmd, "@PlanMemo", DbType.String, mRprjSchedule.PlanMemo);
            _dbHelp.ExecuteNonQuery(cmd);
        }

        public void DelRprjPlanSchedule(MRprjPlanSchedule mRprjSchedule)
        {
            var cmd = _dbHelp.GetSqlStringCommand("DELETE FROM RprjSchedule WHERE RprjID=@RprjID AND AutoNum=@AutoNum");
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, mRprjSchedule.RprjID);
            _dbHelp.AddInParameter(cmd, "@AutoNum", DbType.Int32, mRprjSchedule.AutoNum);
            _dbHelp.ExecuteNonQuery(cmd);
        }

        public void UpdateRprjPlanSchedule(MRprjPlanSchedule mRprjSchedule)
        {
            var cmd = _dbHelp.GetSqlStringCommand("UPDATE RprjSchedule SET ScheduleName=@ScheduleName,ScheduleSort=@ScheduleSort,PlanBegDate=@PlanBegDate,PlanEndDate=@PlanEndDate,PlanMemo=@PlanMemo" +
                                                  " WHERE RprjID=@RprjID AND AutoNum=@AutoNum");

            _dbHelp.AddInParameter(cmd, "@ScheduleName", DbType.String, mRprjSchedule.ScheduleName);
            _dbHelp.AddInParameter(cmd, "@ScheduleSort", DbType.Int32, mRprjSchedule.ScheduleSort);
            _dbHelp.AddInParameter(cmd, "@PlanBegDate", DbType.String, mRprjSchedule.PlanBegDate);
            _dbHelp.AddInParameter(cmd, "@PlanEndDate", DbType.String, mRprjSchedule.PlanEndDate);
            _dbHelp.AddInParameter(cmd, "@PlanMemo", DbType.String, mRprjSchedule.PlanMemo);
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, mRprjSchedule.RprjID);
            _dbHelp.AddInParameter(cmd, "@AutoNum", DbType.Int32, mRprjSchedule.AutoNum);
            _dbHelp.ExecuteNonQuery(cmd);
        }

        public int CountActualSchedule(MRprjPlanSchedule mRprjSchedule)
        {
            var cmd = _dbHelp.GetSqlStringCommand("SELECT COUNT(*) FROM RprjActualSchedule WHERE RprjID=@RprjID AND AutoNum=@AutoNum");
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, mRprjSchedule.RprjID);
            _dbHelp.AddInParameter(cmd, "@AutoNum", DbType.Int32, mRprjSchedule.AutoNum);
            return _dbHelp.ExecuteScalar<int>(cmd);
        }
        
        public void InsertRprjActualSchedule(MRprjActualSchedule actualSchedule)
        {
            var cmd =
                _dbHelp.GetSqlStringCommand(
                    "INSERT INTO RprjActualSchedule VALUES(@RprjID,@AutoNum,@RegDate,@Percentage)");
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, actualSchedule.RprjID);
            _dbHelp.AddInParameter(cmd, "@AutoNum", DbType.Int32, actualSchedule.AutoNum);
            _dbHelp.AddInParameter(cmd, "@RegDate", DbType.String, actualSchedule.RegDate);
            _dbHelp.AddInParameter(cmd, "@Percentage", DbType.Double, actualSchedule.Percentage);
            _dbHelp.ExecuteNonQuery(cmd);
        }

        public void DelRprjActualSchedule(MRprjActualSchedule actualSchedule)
        {
            var cmd =
               _dbHelp.GetSqlStringCommand(
                   "DELETE FROM RprjActualSchedule WHERE RprjID=@RprjID and AutoNum=@AutoNum and RegDate=@RegDate");
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, actualSchedule.RprjID);
            _dbHelp.AddInParameter(cmd, "@AutoNum", DbType.Int32, actualSchedule.AutoNum);
            _dbHelp.AddInParameter(cmd, "@RegDate", DbType.String, actualSchedule.RegDate);
            _dbHelp.ExecuteNonQuery(cmd);
        }

        public MRprjPlanSchedule GetmRprjPlanScheduleByRprjIDAutoNum(int rPrjID, int autoNum)
        {
            MRprjPlanSchedule prjPlanSchedule;
            var cmd = _dbHelp.GetSqlStringCommand(@"select RprjID, AutoNum, ScheduleName, ScheduleSort, PlanBegDate, PlanEndDate, PlanMemo 
                                                    from RprjSchedule
                                                    where RprjID=@RprjID and AutoNum=@AutoNum");
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, rPrjID);
            _dbHelp.AddInParameter(cmd, "@AutoNum", DbType.Int32, autoNum);
            using (var idr = _dbHelp.ExecuteReader(cmd))
            {
                idr.Read();
                prjPlanSchedule = new MRprjPlanSchedule
                                          {
                                              RprjID = idr.GetInt32(0),
                                              AutoNum = idr.GetInt32(1),
                                              ScheduleName = idr.GetString(2),
                                              ScheduleSort = idr.GetInt32(3),
                                              PlanBegDate =idr.GetDateTime(4),
                                                 // idr.IsDBNull(4) ? null : idr.GetDateTime(4).ToString("yyyy-MM-dd"),
                                              PlanEndDate =idr.GetDateTime(5),
                                              PlanMemo = idr.GetString(6)
                                          };
            }

            return prjPlanSchedule;
        }

        public void WriteUploadData(int rPrjID, int dataTypeID, string dataName, string dataPath, string uploadDate)
        {
            var cmd =
                _dbHelp.GetSqlStringCommand(
                    "INSERT INTO RprjData VALUES(@RprjID,@DataTypeID,@DataName,@DataPath,@UploadDate)");
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, rPrjID);
            _dbHelp.AddInParameter(cmd, "@DataTypeID", DbType.Int32, dataTypeID);
            _dbHelp.AddInParameter(cmd, "@DataName", DbType.String, dataName);
            _dbHelp.AddInParameter(cmd, "@DataPath", DbType.String, dataPath);
            _dbHelp.AddInParameter(cmd, "@UploadDate", DbType.String, uploadDate);

            _dbHelp.ExecuteNonQuery(cmd);
        }


    }
}
